﻿module Spreadsheet
open System
open System.Collections.Generic

let wbk = new Dictionary<string, obj>(47) :> IDictionary<string,obj>   

/// Return the code corresponding to the coloumn number x, specified as parameter
/// Examples: col 1 ="A"; col 29 ="AC"; col 16384 = "XFD" 
let private col x =
    if x>16384 || x<1 then invalidArg (sprintf "x (%d)"x) "Expected value between 1 and 2^14"
    let mutable str = ""
    let mutable numb = x
    while numb > 0 do
        let m = (numb-1)%26
        str <- (char (65+m)).ToString() + str
        numb <- (numb-m)/26
    str
   
   
/// Transform a A1-style to (#Coloumn,#Row) numbers
let private A1toCR (str:string) =
    let col = new System.Text.StringBuilder("@@")
    let mutable i = 0
    while str.Length>i && str.[i]>='A' do
        col.Append(str.[i].ToString().ToUpper()) |>ignore
        i<-i+1
    let c = (int col.[col.Length-1]-64) + 26*(int col.[col.Length-2]-64) + 26*26*(int col.[col.Length-3]-64)
    (c, int str.[i..])



type IRange = 
    abstract member ValueSeq : unit -> seq<obj>
    abstract member Values : unit -> obj[,]
    

type Range (completeRng:string) =
    let sheet,cells = 
        let temp = completeRng.Split('!')
        temp.[0], temp.[1]


    interface IRange with
        member this.Values() =
            let ends = cells.Split([|':'|])
            if ends.Length=1 then 
                let el = try (wbk.[sheet+"_"+ends.[0]]) with _ -> box nan
                Array2D.create 1 1 el
            else
                let (col1,row1) = A1toCR ends.[0]
                let (col2,row2) = A1toCR ends.[1]
                Array2D.init (row2-row1+1) (col2-col1+1) (fun x y -> 
                                                            try 
                                                                wbk.[ sheet+"_"+(col (y+col1))+((x+row1).ToString()) ]
                                                            with _ -> box nan )
        member this.ValueSeq() = 
            seq{
                let ends = cells.Split([|':'|])
                if ends.Length=1 then yield wbk.[sheet+"_"+ends.[0]]
                else
                    let (col1,row1) = A1toCR ends.[0]
                    let (col2,row2) = A1toCR ends.[1]
                    for i in col1..col2 do
                        for j in row1..row2 do
                            yield wbk.[sheet+"_"+(col i)+j.ToString()]
            }

type RangeTXT (path:string) =
    interface IRange with
        member this.ValueSeq() =
            seq{  
                let txt = System.IO.File.OpenText(path)
                while not txt.EndOfStream do 
                    let line = txt.ReadLine()
                    let numb = ref 0.
                    if Double.TryParse(line, numb) then 
                        yield box !numb
                    else yield box line
                txt.Close()
            }
            (*Seq.unfold (fun line -> if line = null then
                                        reader.Close()
                                        None 
                                    else
                                        let numb = ref 0.
                                        if Double.TryParse(line, numb) then
                                            Some(box !numb, reader.ReadLine() )
                                        else Some(box line, reader.ReadLine() )
                       ) (reader.ReadLine())
                       *)
        member this.Values() =
            array2D (seq{ yield ((this:>IRange).ValueSeq()) } )


type RangeFromCol (columns:obj[][]) = 
    interface IRange with
        member this.ValueSeq() = 
            seq{
                for c in columns do
                    for r in c do
                        yield r                    
            }

        member this.Values() =
            Array2D.init (columns.Length) (columns.[0].Length) (fun x y -> columns.[x].[y])


type Rif (value:obj) =
    member this.ValueF = 
        try
            match value with
             | :? float as f -> f
             | :? string as s -> float s
             | :? bool as b -> if b then 1. else 0.
             | _ -> nan
        with _ -> nan
    
    override this.ToString() =
        value.ToString()
            
    interface IRange with
        member this.ValueSeq() = seq {yield value}
        member this.Values() = Array2D.create 1 1 value

    static member op_Explicit (x:Rif) = x.ValueF
    (* l'uso di op_Explicit vanifica i seguenti *)
    static member (+) (x:Rif,y:float) = x.ValueF + y
    static member (+) (x:float,y:Rif) = x + y.ValueF
    static member (+) (x:Rif,y:Rif)   = x.ValueF + y.ValueF
    static member (-) (x:Rif,y:float) = x.ValueF - y
    static member (-) (x:float,y:Rif) = x - y.ValueF
    static member (-) (x:Rif,y:Rif)   = x.ValueF - y.ValueF
    static member (*) (x:Rif,y:float) =  x.ValueF*y
    static member (*) (x:float,y:Rif) =  x*y.ValueF
    static member (*) (x:Rif,y:Rif)   =  x.ValueF*y.ValueF
    static member (/) (x:Rif,y:float) =  x.ValueF/y
    static member (/) (x:float,y:Rif) =  x/y.ValueF
    static member (/) (x:Rif,y:Rif)   =  x.ValueF/y.ValueF
    static member ( **) (x:Rif,y:float) =  x.ValueF**y
    static member ( **) (x:float,y:Rif) =  x**y.ValueF
    static member ( **) (x:Rif,y:Rif)   =  x.ValueF**y.ValueF

    